{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercise - Load, Inspect and Clean Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this exercise, you will apply the data preparation skills you have learned so far. The dataset is a modified version of the [\"Housing Prices Dataset\" from Kaggle](https://www.kaggle.com/datasets/yasserh/housing-prices-dataset)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DO NOT MODIFY - imports\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Load and inspect the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the file `Housing_Modified.csv`, check its dimensions and display its first few rows. Feel free to inspect it further using your favorite methods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(547, 13)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# FILL IN - Load the CSV file and check its dimensions\n",
    "df = pd.read_csv(\"Housing_Modified.csv\")\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>price</th>\n",
       "      <th>area</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>stories</th>\n",
       "      <th>mainroad</th>\n",
       "      <th>guestroom</th>\n",
       "      <th>basement</th>\n",
       "      <th>hotwaterheating</th>\n",
       "      <th>airconditioning</th>\n",
       "      <th>parking</th>\n",
       "      <th>prefarea</th>\n",
       "      <th>furnishingstatus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13300000</td>\n",
       "      <td>7420.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>2.0</td>\n",
       "      <td>yes</td>\n",
       "      <td>furnished</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12250000</td>\n",
       "      <td>8960.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>3.0</td>\n",
       "      <td>no</td>\n",
       "      <td>furnished</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12250000</td>\n",
       "      <td>9960.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>2.0</td>\n",
       "      <td>yes</td>\n",
       "      <td>semi-furnished</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12215000</td>\n",
       "      <td>7500.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>3.0</td>\n",
       "      <td>yes</td>\n",
       "      <td>furnished</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11410000</td>\n",
       "      <td>7420.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>2.0</td>\n",
       "      <td>no</td>\n",
       "      <td>furnished</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      price    area  bedrooms  bathrooms  stories mainroad guestroom basement  \\\n",
       "0  13300000  7420.0         4          2        3      yes        no       no   \n",
       "1  12250000  8960.0         4          4        4      yes        no       no   \n",
       "2  12250000  9960.0         3          2        2      yes        no      yes   \n",
       "3  12215000  7500.0         4          2        2      yes        no      yes   \n",
       "4  11410000  7420.0         4          1        2      yes       NaN      yes   \n",
       "\n",
       "  hotwaterheating airconditioning  parking prefarea furnishingstatus  \n",
       "0              no             yes      2.0      yes        furnished  \n",
       "1              no             yes      3.0       no        furnished  \n",
       "2              no              no      2.0      yes   semi-furnished  \n",
       "3              no             yes      3.0      yes        furnished  \n",
       "4              no             yes      2.0       no        furnished  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# FILL IN - Display the first 5 rows\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Confirm that the data types in all columns make sense."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "price                 int64\n",
       "area                float64\n",
       "bedrooms              int64\n",
       "bathrooms             int64\n",
       "stories               int64\n",
       "mainroad             object\n",
       "guestroom            object\n",
       "basement             object\n",
       "hotwaterheating      object\n",
       "airconditioning      object\n",
       "parking             float64\n",
       "prefarea             object\n",
       "furnishingstatus     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# FILL IN - Check data types\n",
    "df.dtypes   # Or df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check for duplicated rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.int64(2)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# FILL IN - Check for duplicates\n",
    "df.duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check how many missing values there are in each column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "price                0\n",
       "area                 4\n",
       "bedrooms             0\n",
       "bathrooms            0\n",
       "stories              0\n",
       "mainroad             0\n",
       "guestroom           19\n",
       "basement             0\n",
       "hotwaterheating      4\n",
       "airconditioning      0\n",
       "parking              0\n",
       "prefarea             0\n",
       "furnishingstatus     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# FILL IN - Check for missing values\n",
    "df.isnull().sum()   # Or df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop duplicated rows, if there were any."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# FILL IN - Drop duplicates, if any\n",
    "df.drop_duplicates(inplace=True)    # Or df = df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When you checked earlier, you should have seen some missing values in the `guestroom` and `hotwaterheating` columns.  \n",
    "\n",
    "Imagine you have a **lot** of missing values for `guestroom` (a lot more than you actually see here) and cannot reliably impute them. You also figure it is not too important a feature for predicting house prices and decide to drop the _column_ entirely."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# FILL IN - Drop the `guestroom` column\n",
    "df.drop(columns=[\"guestroom\"], inplace=True)  # Or df = df.drop(columns=[\"guestroom\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There should be only a few missing values in `hotwaterheating`. Drop the _rows_ of data that are missing values for `hotwaterheating`.  \n",
    "**HINT:** Specify the column name using the `subset` parameter of the `dropna()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# FILL IN - Drop rows with missing values for `hotwaterheating`\n",
    "df.dropna(subset=[\"hotwaterheating\"], inplace=True)    # Or df = df.dropna(subset=[\"hotwaterheating\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Impute missing `area` values using the mean of the column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# FILL IN - Fill missing values for `area` with the mean\n",
    "area_mean = df[\"area\"].mean()\n",
    "df[\"area\"] = df[\"area\"].fillna(area_mean)   # Avoid using inplace=True here due to the FutureWarning that will be raised"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Verify that there are no more missing values and that the dimensions of the resulting cleaned dataset match your expectations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "price               0\n",
       "area                0\n",
       "bedrooms            0\n",
       "bathrooms           0\n",
       "stories             0\n",
       "mainroad            0\n",
       "basement            0\n",
       "hotwaterheating     0\n",
       "airconditioning     0\n",
       "parking             0\n",
       "prefarea            0\n",
       "furnishingstatus    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# FILL IN - Check for missing values\n",
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(541, 12)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# FILL IN - Check the dimensions of the DataFrame\n",
    "df.shape"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
